package simple.xa;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;
import javax.transaction.Status;
import javax.transaction.UserTransaction;

import org.apache.commons.codec.net.BCodec;
import org.apache.log4j.Logger;
import org.postgresql.largeobject.LargeObject;
import org.postgresql.largeobject.LargeObjectManager;

import junit.framework.Assert;

import com.atomikos.icatch.jta.UserTransactionImp;
import com.atomikos.jdbc.AtomikosDataSourceBean;

import edu.cs.usask.ca.silver.persistence.datasource.AbstractStorage;
import edu.cs.usask.ca.silver.persistence.datasource.DataSourceFactory;

/**
 * Yet another technical veryfication subproject.
 * 
 * @author yudi
 *
 */

public class XaTransportDerby extends AbstractStorage {
	// the globally unique resource name for the DB; change if needed
	private String resourceNameRemote = "samples/jdbc/remoteDerbySQLJdbcPooledDatabase";
	private String resourceNameLocal = "samples/jdbc/localDerbySQLJdbcPooledDatabase";
	private Logger logger = Logger.getLogger(XaTransportDerby.class);

	/**
	 * Instance of org.apache.commons.codec.net.BCodec for encoding any String
	 * that is supposed to be stored in the DB.
	 */
	private BCodec myBCodec = new BCodec();

	private DataSource remoteDs = null;

	private DataSource localDs = null;

	public final String LOCAL_FILE_INSERT = "INSERT INTO File (f_name, f_description, f_data) "
			+ "VALUES (?, ?, ?)";

	public final String REMOTE_FILE_INSERT = "INSERT INTO File (f_name, f_description, f_oid) VALUES (?, ?, ?)";

	public final String LOCAL_FILE_SELECT_DATA = "SELECT f_data FROM File WHERE f_id = %d";

	public final String REMOTE_FILE_SELECT_DATA = "SELECT f_oid FROM File WHERE f_id = %d";

	public final String FILE_SELECT_NAME = "SELECT f_name FROM File WHERE f_id = %d";

	public final String FILE_SELECT_DESC = "SELECT f_description FROM File WHERE f_id = %d";

	public final String FILE_SELECT_ALL = "SELECT * FROM File WHERE f_id = %d";
	
	public XaTransportDerby() {}

	public DataSource getRemoteDataSource() {
		if (remoteDs == null) {
			remoteDs = DataSourceFactory.createNetworkClientDerbyDataSource(
					"SilverXaTest", "localhost", 1527, "yudi", "test", 50, 100);
		}
		return remoteDs;
	}

	public DataSource getLocalDataSource() {

		if (localDs == null) {
			localDs = DataSourceFactory.createEmbeddedDerbyDataSource(
					"/Users/yudi/silverdb/XaTest", "test", "test", 10, 100);
		}
		return localDs;
	}

	/**
	 * Utility method to close the connection and terminate the transaction.
	 * This method does all XA related tasks and should be called within a
	 * transaction. When it returns, the transaction will be terminated.
	 * 
	 * @param conn
	 *            The connection.
	 * @param error
	 *            Indicates if an error has occurred or not. If true, the
	 *            transaction will be rolled back. If false, the transaction
	 *            will be committed.
	 */

	public void closeConnection(Connection conn) throws Exception {
		if (conn != null)
			conn.close();
	}

	/**
	 * Updated for Large Object Descriptor (OID type) in PostgreSQL It will
	 * never overload the server memory loading large objects (file).
	 * 
	 * @param fName
	 * @param fDescr
	 * @param fileSourceLocation
	 * @return
	 * @throws Exception
	 */
	public int addFileToStorage(String fName, String fDescr,
			String fileSourceLocation, DataSource ds) throws Exception {
		String sqlStmt = LOCAL_FILE_INSERT;
		// FILE_INSERT =
		// "INSERT INTO File (f_name, f_description, f_data) VALUES (?, ?, ?)";
		String fDescEnc = myBCodec.encode(fDescr);
		String fNameEnc = myBCodec.encode(fName);
		boolean error = false;
		int key = -1;

		File f = new File(fileSourceLocation);
		if (!f.exists()) {
			error = true;
			throw new IOException(
					"Attempting to save file to DB - File not found: "
							+ fileSourceLocation);
		}
		FileInputStream in = null;

		Connection conn = null;
		PreparedStatement insStmt = null;
		// Create Connection and execute SQL
		try {
			in = new FileInputStream(f);
			byte[] file = new byte[(int) f.length()];
			in.read(file);
			conn = getConnection(ds);

			insStmt = conn.prepareStatement(sqlStmt,
					Statement.RETURN_GENERATED_KEYS);
			insStmt.setString(1, fNameEnc);
			// TODO - this could also be
			// f.getName()
			insStmt.setString(2, fDescEnc);
			insStmt.setBytes(3, file);
			insStmt.executeUpdate();

			// Retrieve the auto generated key(s).
			ResultSet rs = insStmt.getGeneratedKeys();
			if (rs == null) {
				// error = true;
				throw new SQLException("error getting generated key");
			}
			if (rs.next()) {
				key = rs.getInt(1);
			} else
				logger.info("no resultset for getGeneratedKeys");

			// Close statement and Connection
			insStmt.close();

			// logger.info(">>> Generated file key = " + key);
			// return the generated key value
		} catch (SQLException sqlExcept) {
			error = true;
			throw sqlExcept;
		} catch (Exception except) {
			error = true;
			//
			throw except;
		} finally {
			try {
				in.close();
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				error = true;
				throw new Exception("rollback failed in addFile");
			}
		}

		// Default return -1 if adding was unsuccessful
		return (!error) ? key : -1;
	}

	public void initTables(DataSource ds) throws Exception {
		boolean error = false;
		Connection _conn = null;
		try {
			_conn = getConnection(ds);
			Statement _s = _conn.createStatement();

			_s.executeUpdate("CREATE TABLE File ( "
					+ "f_ID			INT 			GENERATED BY DEFAULT AS IDENTITY,"
					+ "f_name			VARCHAR(1000)	NOT NULL, "
					+ "f_description	VARCHAR(1000), " + "f_data			BLOB(1G), "
					+ "CONSTRAINT f_pkey PRIMARY KEY(f_ID)" + ")");

			_s.close();
		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			closeConnection(ds, _conn, error);
		}
		// That concludes setup
	}

	public void dropTables(DataSource ds) throws Exception {

		Connection conn = null;
		Statement stmt = null;
		boolean error = false;

		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			stmt.execute("DROP TABLE File");
			stmt.close();

		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				error = true;
				throw e;
			}
		}

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getFName(int)
	 */
	public String getFNamefromDataSource(int fId, DataSource ds) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(FILE_SELECT_NAME, fId);
		return executeSqlReturnString(sqlStmt, ds);
	}


	public void getLocalFFileToStorage(int fId, File destination, DataSource ds)
			throws Exception {
		String sqlStmt;
		// FILE_SELECT_DATA = "SELECT f_data FROM File WHERE f_id = %d";
		sqlStmt = String.format(LOCAL_FILE_SELECT_DATA, fId);
		boolean error = false;
		Connection conn = null;
		Statement stmt = null;
		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);

			ResultSet rset;
			Blob outFile;
			stmt = conn.createStatement();
			rset = stmt.executeQuery(sqlStmt);
			rset.next();
			outFile = rset.getBlob("f_data");

			rset.close();
			stmt.close();

			FileOutputStream output = new FileOutputStream(destination);
			output.write(outFile.getBytes(1, (int) outFile.length()));
			output.flush();
			output.close();

			logger.info("SAVED: " + destination.getAbsolutePath());

		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw new Exception("roll back failed in getFileToLocalStorage");
			}
		}
	}
	
	public int transferFile(int fId, DataSource fromDs, DataSource toDs) throws Exception {
		String sqlInsertStmt, sqlSelectStmt;
		sqlInsertStmt = LOCAL_FILE_INSERT;
		sqlSelectStmt = String.format(FILE_SELECT_ALL, fId);
		
		
		boolean error = false;
		Connection fromDsConn = null;
		Connection toDsConn = null;
		Statement stmt = null;
		PreparedStatement insStmt = null;
		
		int key = -1;
		
		try {
			fromDsConn = getConnection(fromDs); // start transaction at the same time.
			
			stmt = fromDsConn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlSelectStmt);
			rs.next();
			String fNameEnc = null;
			String fDescEnc = null;
			fNameEnc = rs.getString("f_name");
			fDescEnc = rs.getString("f_description");
			Blob outFile;
			outFile = rs.getBlob("f_data");
			
			toDsConn = getConnection(toDs);		// start transaction at the same time.
			
			// Now insert the row into imageslo
			insStmt = toDsConn.prepareStatement(sqlInsertStmt,
					Statement.RETURN_GENERATED_KEYS);
			insStmt.setString(1, fNameEnc);
			// TODO - this could also be
			// f.getName()
			insStmt.setString(2, fDescEnc);
			insStmt.setBlob(3, outFile);
			insStmt.executeUpdate();

			// Retrieve the auto generated key(s).
			rs = insStmt.getGeneratedKeys();
			if (rs == null) {
				// error = true;
				throw new SQLException("error getting generated key");
			}
			if (rs.next()) {
				key = rs.getInt(1);
			} else
				logger.info("no resultset for getGeneratedKeys");
		
			rs.close();
			stmt.close();
			insStmt.close();
			
		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			try {
				closeConnection(fromDs, fromDsConn, error);
				closeConnection(toDs, toDsConn, error);
			} catch (Exception e2) {
				throw e2;
			}
		}
		
		return (error) ? -1 : key;
	
	}

	private String executeSqlReturnString(String sqlStmt, DataSource ds) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		String retVal = null;
		String decoded = null;
		boolean error = false;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the String value from the result set
			if (rs != null) {
				try {
					// TODO - consider checking to make sure there's only a
					// single row in rs
					while (rs.next()) {
						try {
							retVal = rs.getString(1);
						} catch (SQLException e) {
							error = true;
							throw e;
						}
					}
					// DECODE THE STRING
					decoded = myBCodec.decode(retVal);
				} catch (SQLException e) {
					error = true;
					throw e;
				}
			}

			// Close statement and Connection
			stmt.close();
		} catch (SQLException sqlExcept) {
			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw e;
			}
		}

		return decoded;
	}

}
